<!Doctype html>
<html>

<head>
    <title>oixan的博客</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta http-equiv="Content-Type" content="text/html"  charset="utf-8">
    <meta name="referrer" content="no-referrer" />
    <link rel="stylesheet" type="text/css" href="../css/blog.css">

</head>

<body>
    <div class="head">
        <div class="headnav">
            <div class="naveframe">
                <ul class="navlist">
                    <li>
                        <a href="../index.html">
                            主页
                        </a>
                    </li>
                    <li>
                        <a href="../bloglist.html">
                            博客
                        </a>
                    </li>
                    <li>
                        <a href="../about.html">
                            关于
                        </a>
                    </li>
                </ul>
            </div>
        </div>

        <div class="headtext">
            <div class="headtext1">世界很简单，人类很复杂。</div>
            <div class="headtext2">浮生若梦，寻寻觅觅。</div>
        </div>
    </div>

    <div class="contents-all">
        <div class="contents">

            <div class="frame">
                <div class="title">
                    <h3>vba与access查询，插入数据</h3>
                </div>
                <div class="dtime">时间：2018-12-04</div>
                <div class="detail">
      
                   <ol class=" list-paddingleft-2" style="list-style-type: decimal;">
    <li>
        <p>
            <strong style="font-family: &quot;Microsoft YaHei&quot;; font-size: 14px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">新建一个us.accdb数据，设计表users，字段序号（自增），用户，密码，日期。打开那里选以独占方式打开设置密码，随便做几个数据。</strong>
        </p>
    </li>
    <li>
        <p>
            <strong style="font-family: &quot;Microsoft YaHei&quot;; font-size: 14px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">查询数据。新键 excel表(xlsm启用宏的)，打开宏，双击ThisWorkbook,选Workbook,工具-引用Microsoft Active Data Objects 2.6 Library，每次打开就自动查询表内数据，以下代码</strong>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">Private Sub Workbook_Open()<br/></span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">Dim con As New ADODB.Connection</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">Dim rs As New ADODB.Recordset</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">Cells.ClearContents</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">con.Open &quot;provider=microsoft.ace.oledb.12.0;jet oledb:database password=&#39;123&#39;;data source=&quot; &amp; ThisWorkbook.Path &amp; &quot;\us.accdb&quot;</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">Dim sql As String</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">sql = &quot;select * from users&quot;</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">Set rs = con.Execute(sql)</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">Dim i As Integer</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">For i = 0 To rs.fields.Count - 1</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">Cells(1, i + 1) = rs.fields(i).Name &#39;字段</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">Next</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">Range(&quot;a2&quot;).CopyFromRecordset rs &#39;复制数据</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">Columns.AutoFit</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">rs.Close: Set rs = Nothing&nbsp;</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">con.Close: Set con = Nothing&nbsp;&#39;断开连接</span>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <span style="font-size: 14px;">End Sub</span>
        </p>
    </li>
    <li>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <strong><span style="font-size: 14px;">插入数据。如图插入（active控件）三个输入框（文本框）和按钮（命令按钮）</span></strong>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <strong><span style="font-size: 14px;">点击确认按钮就插入输入的数据。</span></strong>
        </p>
        <p><img src="../img/blogimg/2018120401.jpg" style="width: 80%;"></img></p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <strong><span style="font-size: 14px;"><strong style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);"><span style="font-size: 14px;">宏，新键模块，写一个插入数据函数，如下</span></strong></span></strong>
        </p>
        <p>
            Sub insert_data()
        </p>
        <p>
            Dim con As New ADODB.Connection
        </p>
        <p>
            Dim rs As New ADODB.Recordset
        </p>
        <p>
            Dim u, p, d As String
        </p>
        <p>
            u = ActiveSheet.TextBox1.Text
        </p>
        <p>
            p = ActiveSheet.TextBox2.Text
        </p>
        <p>
            d = ActiveSheet.TextBox3.Text
        </p>
        <p>
            If u = &quot;&quot; Then
        </p>
        <p>
            MsgBox &quot;输入用户&quot;
        </p>
        <p>
            Exit Sub
        </p>
        <p>
            End If
        </p>
        <p>
            con.Open &quot;provider=microsoft.ace.oledb.12.0;jet oledb:database password=&#39;123&#39;;data source=&quot; &amp; ThisWorkbook.Path &amp; &quot;\us.accdb&quot;
        </p>
        <p>
            sql1 = &quot;select * from users where 用户=&#39;&quot; &amp; u &amp; &quot;&#39;&quot;
        </p>
        <p>
            Set rs = con.Execute(sql1)
        </p>
        <p>
            If p = &quot;&quot; Then
        </p>
        <p>
            MsgBox &quot;输入密码&quot;
        </p>
        <p>
            Exit Sub
        </p>
        <p>
            End If
        </p>
        <p>
            If rs.EOF Then
        </p>
        <p>
            If IsDate(d) Then
        </p>
        <p>
            &#39;con.Open &quot;provider=microsoft.ace.oledb.12.0;jet oledb:database password=&#39;123&#39;;data source=&quot; &amp; ThisWorkbook.Path &amp; &quot;\us.accdb&quot;
        </p>
        <p>
            Dim sql As String
        </p>
        <p>
            sql = &quot;insert into users (用户,密码,日期) values (&#39;&quot; &amp; u &amp; &quot;&#39;,&#39;&quot; &amp; p &amp; &quot;&#39;,#&quot; &amp; d &amp; &quot;#)&quot;
        </p>
        <p>
            con.Execute (sql)
        </p>
        <p>
            MsgBox &quot;数据添加完成&quot;
        </p>
        <p>
            rs.Close: Set rs = Nothing
        </p>
        <p>
            con.Close: Set con = Nothing
        </p>
        <p>
            Else:
        </p>
        <p>
            MsgBox &quot;错的日期格式&quot;
        </p>
        <p>
            Exit Sub
        </p>
        <p>
            End If
        </p>
        <p>
            Else
        </p>
        <p>
            MsgBox &quot;有相同用户名&quot;
        </p>
        <p>
            Exit Sub
        </p>
        <p>
            End If
        </p>
        <p>
            End Sub
        </p>
    </li>
    <li>
        <p>
            <strong style="font-family: &quot;Microsoft YaHei&quot;; font-size: 14px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">设计模式，双击确认按钮在出来的函数输入</strong>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            Private Sub CommandButton1_Click()
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            Call insert_data
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            End Sub
        </p>
    </li>
    <li>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            <strong style="font-family: &quot;Microsoft YaHei&quot;; font-size: 14px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">然后上面左边选TextBox1,右边选GotFocus，这样点击用户框，日期框自动带出当天日期</strong>
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            Private Sub TextBox1_GotFocus()
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            ActiveSheet.TextBox3.Text = Date
        </p>
        <p style="font-family: &quot;Microsoft YaHei&quot;; font-size: 12.8px; letter-spacing: 1.5px; white-space: normal; background-color: rgb(255, 255, 255);">
            End Sub
        </p>
    </li>
</ol>
                            
                        
                   
    
          
              </div>
                <a href="javascript:void(0)" onclick="showPg()">返回</a>
            </div>

        </div>
    </div>
    <div class="footer">
        <div class="footertext">©2018,oixan博客</div>
    </div>
    <span id="backtop" onclick="topFunction()">返回顶部</span>
    
</body>
<script  src="../js/fanhuiye.js"></script>

</html>